/**
 * Copyright (c) 2013 Mark Tielemans
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * DISCLAIMER
 * This software is provided "as is" and any expressed or implied warranties, including, but not limited to, the implied warranties of 
 * merchantability and fitness for a particular purpose are disclaimed. In no event shall the regents or contributors be liable for any 
 * direct, indirect, incidental, special, exemplary, or consequential damages (including, but not limited to, procurement of substitute 
 * goods or services; loss of use, data, or profits; or business interruption) however caused and on any theory of liability, whether in 
 * contract, strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this software, even if 
 * advised of the possibility of such damage.
 */
package nl.marktielemans.easydb.generator;

import java.util.Arrays;
import java.util.List;
import java.util.Map.Entry;

import nl.marktielemans.easydb.DatabaseVendor;
import nl.marktielemans.easydb.EasyDBUtils;
import nl.marktielemans.easydb.model.DatabaseTable;
import nl.marktielemans.easydb.model.EasyDBEntity;
import nl.marktielemans.easydb.model.IndexType;
import nl.marktielemans.easydb.model.TableColumn;

import org.apache.log4j.Logger;

/**
 * Generator for MySQL queries for use with {@link DatabaseVendor#MYSQL}.
 * @author Mark Tielemans
 * @version 1.0
 * @since 1.0
 */
public class MySQLQueryGenerator implements QueryGenerator {

	public static final Logger logger = Logger.getLogger(MySQLQueryGenerator.class);

	private static MySQLQueryGenerator instance;
	private MySQLQueryGenerator() {}

	public static MySQLQueryGenerator get() {
		if(instance==null) instance=new MySQLQueryGenerator();
		return instance;
	}

	@Override
	public String generateCreateTableQuery(DatabaseTable table) {
		String query = "CREATE TABLE IF NOT EXISTS `"+table.getName()+"` (";

		// Add column definitions to query
		int i=0;
		for(TableColumn<?> col : table.getColumns()) {
			if(i != 0) {
				query += ", ";
			}
			query += getTableColumnAsMySQLString(col);
			i++;
		}

		// For each known indexType, append definitions to query
		for(Entry<String, List<TableColumn<?>>> entry : EasyDBUtils.getTableColumnsByIndex(table).entrySet()) {
			if(entry.getKey().equals(IndexType.SQL_PK.getSqlText())) {
				// PK has a different syntax than the others
				String listTxt = Arrays.toString(entry.getValue().toArray());
				listTxt = listTxt.substring(1, listTxt.length()-1);		// Cut off first and last char. These are square brackets added by Arrays.toString.
				String queryTxt = ", "+entry.getKey() + " ("+listTxt+")";
				query += queryTxt;
			} else {
				for(TableColumn<?> col : entry.getValue()) {
					query += ", " + entry.getKey() + " ("+col.getPropertyName()+")";
				}
			}

		}

		query += ")";

		logger.debug("Generated create table query: "+query);
		return query;
	}

	@Override
	public String generateDropQuery(DatabaseTable table) {
		String query = "DROP TABLE `"+table.getName()+"`";
		logger.debug("Generated drop table query: "+query);
		return query;
	}

	@Override
	public String generateInsertQuery(DatabaseTable table, EasyDBEntity entity) {
		List<TableColumn<?>> columns = entity.getProperties();

		// Form query
		String query = "INSERT INTO `"+table.getName()+"` (";

		int i=0;
		for(TableColumn<?> col : columns) {
			if(i!=0) query += ", ";
			query += "`"+col.getPropertyName()+"`";
			i++;
		}

		query += ") VALUES (";

		i=0;
		for(TableColumn<?> col : columns) {
			String value = EasyDBUtils.columnValueAsString(col.getValue());
			if(i!=0) query += ", ";
			if(value == null) {
				query += "NULL";
			} else {
				query += "'"+value+"'";
			}
			i++;
		}

		query += ");";

		logger.debug("Generated insert query: "+query);
		return query;
	}

	@Override
	public String generateUpdateQuery(DatabaseTable table, EasyDBEntity entity, List<TableColumn<?>> conditions) {
		String query = "UPDATE `"+table.getName()+"` SET ";

		// Key-value pairs
		int i=0;
		for(TableColumn<?> col : entity.getProperties()) {
			if(i!=0) query += ", ";
			String value = EasyDBUtils.columnValueAsString(col.getValue());
			if(value == null) value = "NULL";
			else value = "'"+value+"'";
			query += "`"+col.getPropertyName()+"` = "+value;
			i++;
		}

		query += " WHERE ";

		i=0;
		for(TableColumn<?> col : conditions) {
			if(i!=0) query += " AND ";
			String value = EasyDBUtils.columnValueAsString(col.getValue());
			if(value == null) value = "NULL";
			else value = "'"+value+"'";
			query += "`"+col.getPropertyName()+"` = "+value;
			i++;
		}

		logger.debug("Generated update query: "+query);		
		return query;
	}

	@Override
	public String generateSimpleSelectQuery(DatabaseTable table, List<TableColumn<?>> conditions) {
		String query = "SELECT * FROM `"+table.getName()+"`";
		if(conditions!=null&&conditions.size()>0) {
			query+=" WHERE ";

			int i=0;
			for(TableColumn<?> col : conditions) {
				if(i!=0) query += " AND ";
				String value = EasyDBUtils.columnValueAsString(col.getValue());
				if(value == null) value = "NULL";
				else value = "'"+value+"'";
				query += "`"+col.getPropertyName()+"` = "+value;
				i++;
			}
		}
		logger.debug("Generated simple select query: "+query);
		return query;
	}

	@Override
	public String generateDeleteQuery(DatabaseTable table,
			List<TableColumn<?>> conditions) {
		String query = "DELETE FROM `"+table.getName()+"` WHERE ";

		int i=0;
		for(TableColumn<?> col : conditions) {
			if(i!=0) query += " AND ";
			String value = EasyDBUtils.columnValueAsString(col.getValue());
			if(value == null) value = "NULL";
			else value = "'"+value+"'";
			query += "`"+col.getPropertyName()+"` = "+value;
			i++;
		}

		logger.debug("Generated delete query: "+query);
		return query;
	}

	/**
	 * Retrieve an SQL representation of a column definition, usable in SQL queries.
	 * @param col column definition
	 * @return textual representation of column definition
	 */
	public String getTableColumnAsMySQLString(TableColumn<?> col) {
		String charlimit="";
		if(col.getLength() != 0) {
			charlimit = "("+col.getLength()+")";
		}

		String notNull="";
		if(!col.getIsNullable()) notNull = "NOT NULL";

		String a_I = "";
		if(col.getIsAutoIncrement()) a_I = " AUTO_INCREMENT";

		String sql = 
				"`"+col.getPropertyName()+"` "
						+ col.getType()
						+ charlimit + " "
						+ notNull
						+ a_I;

		logger.debug("Generated MySQL for column: "+sql);
		return sql;
	}
}
